package spatialmarketplace.cem.dao.implementation;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import net.sourceforge.stripes.action.SimpleMessage;
import net.sourceforge.stripes.util.Log;
import spatialmarketplace.cem.dao.DBHandler;
import spatialmarketplace.cem.dao.ResourceDao;
import spatialmarketplace.cem.model.Group;
import spatialmarketplace.cem.model.Resource;

/**
 * Implement all functions of Resource Database Access Object (ResourceDAO.java)
 *
 * @author Hung
 */
public class ResourceDaoImpl implements ResourceDao {

    private static final Log logger = Log.getInstance(ResourceDaoImpl.class);
    private Connection conn = null;
    private Map<Integer, Resource> map = new HashMap<Integer, Resource>();
    private static ResourceDaoImpl instance = new ResourceDaoImpl();

    public static ResourceDaoImpl getInstance() {
        return instance;
    }

    /**
     * Get all resources in database
     *
     * @return List of all resources
     */
    public List<Resource> read() {
        DBHandler dbInstance = DBHandler.getInstance();
        List<Resource> list = new ArrayList<Resource>();
        Resource resource;
        Integer resourceId;
        String type;
        boolean isPublic;
        Float starRating;
        Integer likesCounter;

        try {
            if (dbInstance != null) {
                String query = new String("select * from CEM_Resource");
                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);

                try {
                    while (rs.next()) {
                        resourceId = Integer.valueOf(rs.getString("resourceId"));
                        type = rs.getString("type");
                        isPublic = Boolean.getBoolean(rs.getString(""));
                        starRating = Float.valueOf(rs.getString("starRating"));
                        /*
                         * Need to check again with the big number
                         */
                        likesCounter = Integer.valueOf(rs.getInt("likesCounter"));

                        resource = new Resource(resourceId, type, isPublic, starRating, likesCounter);

                        list.add(resource);
                        System.out.println("----------------------------------------" + resource);
                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }

        return list;
    }

    /**
     * Get default resource in case of no resource selected
     *
     * @return Resource ID
     */
    public Integer getDefaultResource() {
        Integer resourceId = null;
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                conn = dbInstance.openConnection();
                // Check user has already like or not
                String query = new String("select resourceId from CEM_Resource");
                ResultSet rs = dbInstance.executeQuery(query);
                rs.next();
                resourceId = Integer.valueOf(rs.getInt("resourceId"));
                System.out.println("Error: " + query);
            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }

        return resourceId;
    }

    /**
     * Check user liked resource or not
     *
     * @param resourceId Resource ID
     * @param userId User ID
     * @return yes or no
     */
    public Boolean getLiked(Integer resourceId, Integer userId) {
        Boolean isLiked = new Boolean(true);
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                conn = dbInstance.openConnection();
                // Check user has already like or not
                String query_check = new String("select * from CEM_Resource_User where resourceId=" + resourceId + " and userId=" + userId);
                ResultSet rs = dbInstance.executeQuery(query_check);
                if (rs.next() == false) {
                    isLiked = false;
                } else {
                    // O is false and 1 is true
                    if(Integer.valueOf(rs.getString("likedResource"))==1){
                        isLiked = true;
                    }
                    else { isLiked = false; }
                    
                    System.out.println("isLiked: " + isLiked+":"+rs.getString("likedResource")+":"+rs.getString("resourceId"));
                }
                System.out.println("Error: " + query_check);
            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }
        return isLiked;
    }
    /**
     * Check user has voted resource or not
     * @param resourceId
     * @param userId
     * @return 
     */
    public Boolean getVoted(Integer resourceId, Integer userId) {
        Boolean isVoted = new Boolean(true);
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                conn = dbInstance.openConnection();
                // Check user has already like or not
                String query_check = new String("select * from CEM_Resource_User where resourceId=" + resourceId + " and userId=" + userId);
                ResultSet rs = dbInstance.executeQuery(query_check);
                if (rs.next() == false) {
                    isVoted = false;
                } else {
                    // O is false and 1 is true
                    if((Integer.valueOf(rs.getString("rate"))>=1)&&(Integer.valueOf(rs.getString("rate"))<=5)){
                        isVoted = true;
                    }
                    else { isVoted = false; }
                    
                    System.out.println("isLiked: " + isVoted+":"+rs.getString("likedResource")+":"+rs.getString("resourceId"));
                }
                System.out.println("Error: " + query_check);
            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }
        return isVoted;
    }
    
    
    /**
     * Get voted's value that has done by user with the resource
     *
     * @param userId User ID
     * @param resourceId Resource ID
     * @return voted's value
     */
    public Integer getRatedUserResource(Integer userId, Integer resourceId) {
        Integer rated = null;
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                conn = dbInstance.openConnection();
                // Check user has already like or not
                String query = new String("select * from CEM_Resource_User where resourceId=" + resourceId + " and userId=" + userId);
                ResultSet rs = dbInstance.executeQuery(query);
                rs.next();
                rated = Integer.valueOf(rs.getInt("rate"));
                System.out.println("Error: " + query);
            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }
        return rated;
    }

    /**
     * Update liked counter to database based on number of user's likes
     *
     * @param resourceId Resource ID
     * @param likesCounter number of liked people
     * @param userId User ID
     */
    public void setLikesCounter(Integer resourceId, Integer likesCounter, Integer userId) {
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                conn = dbInstance.openConnection();
                // Check user has already like or not
                String query_check = new String("select * from CEM_Resource_User where resourceId=" + resourceId + " and userId=" + userId);
                ResultSet rs = dbInstance.executeQuery(query_check);
                if (rs.next() == false) {
                    // count on userId
                    String query2 = "Insert into CEM_Resource_User values(" + resourceId + "," + userId + ",true,null)";
                    try {
                        PreparedStatement preparedStatement1 = null;
                        // PreparedStatements can use variables and are more efficient
                        preparedStatement1 = conn.prepareStatement(query2);
                        preparedStatement1.executeUpdate();

                    } catch (SQLException ex) {
                        Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }else{
                        // update like
                        String queryUpdate = new String("update CEM_Resource_User set likedResource=true where userId='" + userId + "' and resourceId=" + resourceId);
                        PreparedStatement preparedStatement = null;
                        preparedStatement = conn.prepareStatement(queryUpdate);
                        preparedStatement.executeUpdate();
                                         
                }
                    // Increase counter engine
                    String query = "Update CEM_Resource set likesCounter=" + likesCounter + " where resourceId=" + resourceId;
                    System.out.println("Error: " + query);
                    try {
                        PreparedStatement preparedStatement = null;
                        // PreparedStatements can use variables and are more efficient
                        preparedStatement = conn.prepareStatement(query);
                        preparedStatement.executeUpdate();
                        dbInstance.closeConnection();

                    } catch (SQLException ex) {
                        Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                    }

                


            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }
    }

    /**
     * Get rating's value from database and calculate new rating based on new
     * user's voted Update latest rating's value to database
     *
     * @param resourceId Resource ID
     * @param rating Rating's value
     */
    public void setRating(Integer resourceId, Float rating) {
        DBHandler dbInstance = DBHandler.getInstance();
        Integer votedNumber = 0;
        Float starRating;
        try {
            if (dbInstance != null) {

                conn = dbInstance.openConnection();
                // Get number of voted people
                String query = new String("select * from CEM_Resource where resourceId=" + resourceId);
                ResultSet rs = dbInstance.executeQuery(query);
                try {
                    rs.next();
                    Integer tempVotedNumber = Integer.valueOf(rs.getString("ratingCount"));
                    Float tempStarRating = Float.valueOf(rs.getFloat("starRating"));
                    // Calculate starRating
                    starRating = ((tempStarRating * tempVotedNumber) + rating) / (tempVotedNumber + 1);
                    starRating = (float)Math.round(starRating*10)/10;
                    votedNumber = tempVotedNumber + 1;
                    
                    // update star rating
                    String queryUpdate = new String("update CEM_Resource set ratingCount=" + votedNumber + ", starRating='" + starRating + "' where resourceId=" + resourceId);
                    PreparedStatement preparedStatement = null;
                    preparedStatement = conn.prepareStatement(queryUpdate);
                    preparedStatement.executeUpdate();
                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }
    }

    /**
     * Update voted's number to resource that is selected by user Save it to
     * database
     *
     * @param userId User ID
     * @param resourceId Resource ID
     * @param rating Rating's value
     */
    public void setRateToUserResource(Integer userId, Integer resourceId, Float rating) {
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                conn = dbInstance.openConnection();

                try {

                    String query_check = new String("select * from CEM_Resource_User where resourceId=" + resourceId + " and userId=" + userId);
                    ResultSet rs = dbInstance.executeQuery(query_check);
                    if (rs.next() == false) {
                        // count on userId
                        String query2 = "Insert into CEM_Resource_User values(" + resourceId + "," + userId + ",false," + rating + ")";
                        try {
                            PreparedStatement preparedStatement1 = null;
                            // PreparedStatements can use variables and are more efficient
                            preparedStatement1 = conn.prepareStatement(query2);
                            preparedStatement1.executeUpdate();

                        } catch (SQLException ex) {
                            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    } else {

                        // update star rating
                        String queryUpdate = new String("update CEM_Resource_User set rate=" + rating + " where userId='" + userId + "' and resourceId=" + resourceId);
                        PreparedStatement preparedStatement = null;
                        preparedStatement = conn.prepareStatement(queryUpdate);
                        preparedStatement.executeUpdate();
                        dbInstance.closeConnection();
                    }

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }
    }

    /**
     * Get all information about one resource based on Resource ID
     *
     * @param resourceId Resource ID
     * @return resource's detail
     */
    public Resource read_Resource(Integer resourceId) {
        DBHandler dbInstance = DBHandler.getInstance();
        Resource resource = null;
        // resource = new Resource(101,"Data",true,Float.valueOf("4.5"),231);

        String type;
        boolean isPublic;
        Float starRating;
        Integer likesCounter;
        Integer licenseId;
        String layerInformationText;
        String downloadText;
        String stylesText;
        String mapsText;
        String name;
        String url_resource;
        try {
            if (dbInstance != null) {
                String query = new String("select * from CEM_Resource where resourceId=" + resourceId);
                String query2 = new String("select * from SM_Resource where resourceId=" + resourceId);
                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);
                ResultSet rs2 = dbInstance.executeQuery(query2);
                try {
                    rs.next();
                    rs2.next();
                    resourceId = Integer.valueOf(rs.getString("resourceId"));
                    type = rs.getString("type");
                    isPublic = Boolean.getBoolean(rs.getString("isPublic"));
                    starRating = Float.valueOf(rs.getString("starRating"));
                    likesCounter = Integer.valueOf(rs.getInt("likesCounter"));

                    licenseId = Integer.valueOf(rs2.getInt("licenseId"));
                    layerInformationText = rs2.getString("layerInformationText");
                    downloadText = rs2.getString("downloadText");
                    stylesText = rs2.getString("stylesText");
                    mapsText = rs2.getString("mapsText");
                    name = rs2.getString("name");
                    url_resource = rs2.getString("url_resource");

                    resource = new Resource(resourceId, type, isPublic, starRating, likesCounter, licenseId, layerInformationText, downloadText, stylesText, mapsText, name, url_resource);

                    System.out.println("----------------------------------------" + resource);

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }


        return resource;
    }

    /**
     * Update resource's data to database
     *
     * @param resource
     */
    public void save(Resource resource) {
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                String query = "INSERT INTO  CEM_Resource(resourceId,type,isPublic, starRating,likesCounter)";
                conn = dbInstance.openConnection();

                try {
                    PreparedStatement preparedStatement = null;
                    // PreparedStatements can use variables and are more efficient
                    preparedStatement = conn.prepareStatement(query);
                    // Parameters start with 1
                    preparedStatement.setInt(1, resource.getResourceId());
                    preparedStatement.setString(2, String.valueOf(resource.getType()));
                    preparedStatement.setString(3, String.valueOf(resource.getPublic() ? 1 : 0));
                    preparedStatement.setFloat(4, resource.getStarRating());
                    preparedStatement.setInt(5, resource.getLikesCounter());
                    preparedStatement.execute();

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }

    }

    /**
     * Assign which groups are shared the resource and update to database
     *
     * @param groupId Group ID
     * @param resourceId Resource ID
     */
    public void insertGroupShareResource(Integer groupId, Integer resourceId) {
        DBHandler dbInstance = DBHandler.getInstance();
        try {
            if (dbInstance != null) {

                String query = "INSERT INTO  Groups_Resources(resourceId,groupId) values(" + resourceId + "," + groupId + ")";
                conn = dbInstance.openConnection();
                try {

                    PreparedStatement preparedStatement1 = null;
                    // PreparedStatements can use variables and are more efficient
                    preparedStatement1 = conn.prepareStatement(query);
                    preparedStatement1.executeUpdate();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }


            }
        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }
    }

    /**
     * List all groups are shared the resource based on Resource ID All
     * information about group will be displayed
     *
     * @param resourceId Resource ID
     * @return
     */
    public List<Group> listGroupsSharing(Integer resourceId) {
        List<Group> groupsSharing = new ArrayList<Group>();
        Group group;
        Integer groupId;
        String name;
        boolean isPublic;
        boolean memberCanInvite;
        boolean forumIsPublic;
        boolean memberCanPublishResources;
        boolean adsEnabled;
        String logo;
        String description;
        String accessType;
        String keyword;

        DBHandler dbInstance = DBHandler.getInstance();
        String query = new String("SELECT c.groupId AS groupId, c.name AS NAME, c.isPublic AS isPublic, c.memberCanInvite AS memberCanInvite,"
                + "c.forumIsPublic AS forumIsPublic, c.memberCanPublishResources AS memberCanPublishResources, "
                + "c.adsEnabled AS adsEnabled, c.logo AS logo, c.accessType AS accessType, c.keyword AS keyword,"
                + "c.description AS description "
                + "FROM CEM_Group c "
                + "WHERE c.groupId IN (SELECT groupId FROM Groups_Resources WHERE resourceId=" + resourceId + ")"
                + "ORDER BY c.groupId DESC");

        dbInstance.openConnection();
        ResultSet rs = dbInstance.executeQuery(query);
        try {
            while (rs.next()) {
                groupId = Integer.valueOf(rs.getString("groupId"));
                name = rs.getString("name");
                isPublic = Boolean.getBoolean(rs.getString("isPublic"));
                memberCanInvite = Boolean.getBoolean(rs.getString("memberCanInvite"));
                forumIsPublic = Boolean.getBoolean(rs.getString("forumIsPublic"));
                memberCanPublishResources = Boolean.getBoolean(rs.getString("memberCanPublishResources"));
                adsEnabled = Boolean.getBoolean(rs.getString("adsEnabled"));
                logo = rs.getString("logo");
                description = rs.getString("description");
                accessType = rs.getString("accessType");
                keyword = rs.getString("keyword");

                group = new Group(groupId, name, isPublic, memberCanInvite, forumIsPublic, memberCanPublishResources,
                        adsEnabled, logo, description, accessType, keyword);

                groupsSharing.add(group);

            }

            dbInstance.closeConnection();

        } catch (SQLException ex) {
            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return groupsSharing;
    }

    /**
     * List all groups are not shared the resource
     *
     * @param resourceId Resource ID
     * @return
     */
    public List<Group> listGroupsUnSharing(Integer resourceId,Integer userId) {
        List<Group> groupsUnSharing = new ArrayList<Group>();
        Group group;
        Integer groupId;
        String name;
        boolean isPublic;
        boolean memberCanInvite;
        boolean forumIsPublic;
        boolean memberCanPublishResources;
        boolean adsEnabled;
        String logo;
        String description;
        String accessType;
        String keyword;

        DBHandler dbInstance = DBHandler.getInstance();
        String query = new String("SELECT c.groupId AS groupId, c.name AS NAME, c.isPublic AS isPublic, c.memberCanInvite AS memberCanInvite,"
                + "c.forumIsPublic AS forumIsPublic, c.memberCanPublishResources AS memberCanPublishResources, "
                + "c.adsEnabled AS adsEnabled, c.logo AS logo, c.accessType AS accessType, c.keyword AS keyword,"
                + "c.description AS description "
                + "FROM CEM_Group c "
                + "WHERE c.groupId NOT IN (SELECT groupId FROM Groups_Resources WHERE resourceId=" + resourceId + ") "
                + "AND c.groupId IN(SELECT groupId FROM CEM_GroupMember WHERE userId="+userId+")"
                + "ORDER BY c.groupId DESC");

        dbInstance.openConnection();
        ResultSet rs = dbInstance.executeQuery(query);
        try {
            while (rs.next()) {
                groupId = Integer.valueOf(rs.getString("groupId"));
                name = rs.getString("name");
                isPublic = Boolean.getBoolean(rs.getString("isPublic"));
                memberCanInvite = Boolean.getBoolean(rs.getString("memberCanInvite"));
                forumIsPublic = Boolean.getBoolean(rs.getString("forumIsPublic"));
                memberCanPublishResources = Boolean.getBoolean(rs.getString("memberCanPublishResources"));
                adsEnabled = Boolean.getBoolean(rs.getString("adsEnabled"));
                logo = rs.getString("logo");
                description = rs.getString("description");
                accessType = rs.getString("accessType");
                keyword = rs.getString("keyword");

                group = new Group(groupId, name, isPublic, memberCanInvite, forumIsPublic, memberCanPublishResources,
                        adsEnabled, logo, description, accessType, keyword);

                groupsUnSharing.add(group);

            }

            dbInstance.closeConnection();

        } catch (SQLException ex) {
            Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
        return groupsUnSharing;
    }

    /**
     * Search resource based on keywords and return all resource which match
     * with keyword
     *
     * @param keywords
     * @return list of matched resources
     */
    @Override
    public List<Resource> search(String keywords) {
        DBHandler dbInstance = DBHandler.getInstance();
        List<Resource> resourceSearched = new ArrayList<Resource>();
        Resource resource;
        Integer resourceId;
        String type;
        boolean isPublic;
        Float starRating;
        Integer likesCounter;
        try {
            if (dbInstance != null) {
                String query = new String("select * from CEM_Resource like='%'" + keywords);
                dbInstance.openConnection();
                ResultSet rs = dbInstance.executeQuery(query);

                try {
                    while (rs.next()) {
                        /*
                         * get the results
                         */

                        resource = new Resource();

                        resourceSearched.add(resource);
                        System.out.println("----------------------------------------" + resource);
                    }

                    dbInstance.closeConnection();

                } catch (SQLException ex) {
                    Logger.getLogger(DBHandler.class.getName()).log(Level.SEVERE, null, ex);
                }

            }


        } catch (Exception e) {
            System.out.println("Error: " + e);
            logger.error("ERROR: " + ResourceDaoImpl.class.getName() + ", " + e);
        }
        return resourceSearched;
    }
}